<?php
include_once 'template/header_index.php';
// 参考：http://www.php.net/manual/zh/pdo.connections.php
// https://segmentfault.com/a/1190000004106175

include_once 'data/db_config.php';
try {
    $dbh = new PDO('mysql:host=localhost;dbname=' . $db['db_name'], $db['user'], $db['pwd'], array(PDO::ATTR_PERSISTENT => true));
    $dbh->query('set names utf8');

    // 获取昨日的总户总量
    $sql_user = 'SELECT cumulate_user FROM `usercumulate` order by ref_date desc limit 1';
    $res = $dbh->query($sql_user);
    $total_user = $res->fetchColumn();

    // 获取总文章数量
    $sql_article = 'SELECT title FROM `wx`.`articlesummary` GROUP BY title;';
    $res1 = $dbh->query($sql_article);
    $total_article = count($res1->fetchAll());

    //  获取过去7天的用户数据
    $next_day = date("Y-m-d", strtotime('-7 days'));
    $end_day = date('Y-m-d', strtotime('-1 days'));
    $sql_users = 'SELECT * FROM `user` where ref_date>="' . $next_day . '" and ref_date<="' . $end_day . '"';

    //  获取过去7天的图文数据
    $sql_article = 'SELECT ref_date,msgid FROM `articlesummary` where ref_date>="' . $next_day . '" and ref_date<= "' . $end_day . '" group by msgid';

    // 获取博客最新一次统计的日期
    $sql_blog_date = 'SELECT stat_date FROM `blog` order by stat_date desc limit 1';
    $res2 = $dbh->query($sql_blog_date);
    $blog_date = $res2->fetchColumn();
    $sql_blog = 'SELECT * FROM `blog` where stat_date="' . $blog_date . '"';

    // 获取微信群最新一次统计的日期
    $sql_wx_date = 'SELECT stat_date FROM `wx_group` order by stat_date desc limit 1';
    $res3 = $dbh->query($sql_wx_date);
    $wx_date = $res3->fetchColumn();    
    $sql_wx_group = 'SELECT * FROM `wx_group` where stat_date="' . $wx_date . '"';
    ?>
    <div class="col-sm-9 col-md-10 main">
        <h1><strong id="chart_title"><?= $name ?></strong></h1>
        <hr>
        <h2 class="sub-header">近7天<span style="color: #c00;">用户数据</span></h2>
        <div class="row placeholders">
            <div class="col-xs-6 col-sm-2 placeholder" style="height: 180px; background-color:#0b8d9a;border-radius: 50%;color: #fff;font-weight: bold;font-size: 18px;">
                <br><br><br>
                <span><?= $total_user ?></span>
                <br>
                <p>总粉丝数</p>
            </div>
            <div class="table-responsive col-xs-6 col-sm-8 placeholder">
                <table class="table table-bordered  table-hover table-condensed">
                    <thead><tr style='font-weight:bold;'><td>日期</td><td>新增用户数</td><td>取消用户数</td><td>净增用户数</td><td>总用户数</td></tr></thead>
                    <tbody>
                        <?php
                        $new = 0;
                        $cancel = 0;
                        $net = 0;
                        $total = 0;
                        foreach ($dbh->query($sql_users) as $row) {
                            echo '<tr>';
                            echo '<td>' . $row['ref_date'] . '</td>';
                            echo '<td>' . $row['new_user'] . '</td>';
                            echo '<td>' . $row['cancel_user'] . '</td>';
                            if ($row['net_user'] >= 50) {
                                echo '<td style="background-color:#FFAF3E;">' . $row['net_user'] . '</td>';
                            } else {
                                echo '<td>' . $row['net_user'] . '</td>';
                            }
                            echo '<td>' . $row['cumulate_user'] . '</td>';
                            echo '</tr>';
                            $new += intval($row['new_user']);
                            $cancel += intval($row['cancel_user']);
                            $net += intval($row['net_user']);
                            $total = intval($row['cumulate_user']);
                        }
                        echo '<tr style="font-weight:bold;color:#0B8D9A;"><td>Total</td><td>' . $new . '</td><td>' . $cancel . '</td><td>' . $net . '</td><td>-</td></tr>';
                        ?>
                    </tbody>
                </table>
                <p><em>备注：</em>黄色单元格表示净增用户数大于50.</p>
            </div>            
        </div>
        <h2 class="sub-header">近14天<span style="color: #c00;">图文数据</span></h2>
        <div class="row placeholders">
            <div class="col-xs-6 col-sm-2 placeholder" style="height: 180px; background-color:#ffaf3e;border-radius: 50%;color: #fff;font-weight: bold;font-size: 18px;">
                <br><br><br>
                <span><?= $total_article ?></span>
                <p>总文章数</p>
            </div>
            <div class="table-responsive col-xs-6 col-sm-9 placeholder">
                <table class="table table-bordered  table-hover table-condensed">
                    <thead><tr style='font-weight:bold;'><td>日期</td><td>文章标题</td><td>图文阅读人数</td><td>原文阅读人数</td><td>分享人数</td><td>收藏人数</td></tr></thead>
                    <tbody>
                        <?php
                        foreach ($dbh->query($sql_article) as $result) {
                            $sub_sql = 'SELECT title,ref_date,sum(int_page_read_user) as iru,sum(int_page_read_count) as irc,sum(ori_page_read_user) as oru,sum(ori_page_read_count) as orc,sum(share_user) as su,sum(share_count) as sc,sum(add_to_fav_user) as afu,sum(add_to_fav_count) as afc FROM `articlesummary` where msgid="' . $result['msgid'] . '" ';
                            foreach ($dbh->query($sub_sql) as $key => $article) {
                                echo '<tr>';
                                echo '<td>' . $article['ref_date'] . '</td>';
                                echo '<td>' . $article['title'] . '</td>';
                                if ($article['iru'] >= 1000) {
                                    echo '<td style="background-color:#FFAF3E;">' . $article['iru'] . '</td>';
                                } else {
                                    echo '<td>' . $article['iru'] . '</td>';
                                }
                                echo '<td>' . $article['oru'] . '</td>';
                                echo '<td>' . $article['su'] . '</td>';
                                echo '<td>' . $article['afu'] . '</td>';
                                echo '</tr>';
                            }
                        }
                        ?>
                    </tbody>
                </table>
                <p><em>备注：</em>黄色单元格表示阅读数大于1000.</p>
            </div>            
        </div>
        <h2 class="sub-header"><?= $blog_date ?> <span style="color: #c00;">CSDN博客数据</span></h2>
        <div class="row placeholders">
            <!--            <div class="col-xs-6 col-sm-2 placeholder" style="height: 180px; background-color:#ffaf3e;border-radius: 50%;color: #fff;font-weight: bold;font-size: 18px;">
                            <br><br><br>
                            <span><?= $total_article ?></span>
                            <p>总阅读数</p>
                        </div>-->
            <div class="table-responsive col-xs-6 col-sm-9 placeholder">
                <p><em>备注：</em>黄色单元格表示阅读量大于5000.</p>
                <table class="table table-bordered  table-hover table-condensed">
                    <thead><tr style='font-weight:bold;'><td>作者</td><td>URL</td><td>访问量</td><td>积分</td><td>原创</td><td>转载</td><td>译文</td><td>评论</td></tr></thead>
                    <tbody>
                        <?php
                        $visit = 0;
                        $integration = 0;
                        $original = 0;
                        $reprint = 0;
                        $version = 0;
                        $comment = 0;
                        foreach ($dbh->query($sql_blog) as $blog) {
                            echo '<tr>';
                            echo '<td>' . $blog['author'] . '</td>';
                            echo '<td style="text-align:left;">' . $blog['url'] . '</td>';
                            if ($blog['visit'] >= 5000) {
                                echo '<td style="background-color:#FFAF3E;">' . $blog['visit'] . '</td>';
                            } else {
                                echo '<td>' . $blog['visit'] . '</td>';
                            }
                            echo '<td>' . $blog['integration'] . '</td>';
                            echo '<td>' . $blog['original'] . '</td>';
                            echo '<td>' . $blog['reprint'] . '</td>';
                            echo '<td>' . $blog['version'] . '</td>';
                            echo '<td>' . $blog['comment'] . '</td>';
                            echo '</tr>';
                            $visit += intval($blog['visit']);
                            $integration += intval($blog['integration']);
                            $original += intval($blog['original']);
                            $reprint += intval($blog['reprint']);
                            $version += intval($blog['version']);
                            $comment += intval($blog['comment']);
                        }
                        echo '<tr style="font-weight:bold;color:#0B8D9A;"><td colspan="2">Total</td><td>' . $visit . '</td><td>' . $integration . '</td><td>' . $original . '</td><td>'.$reprint.'</td><td>'.$version.'</td><td>'.$comment.'</td></tr>';
                        ?>
                    </tbody>
                </table>
            </div>            
        </div>
        <h2 class="sub-header"><?= $wx_date ?> <span style="color: #c00;">微信群数据</span></h2>
        <div class="row placeholders">
            <!--            <div class="col-xs-6 col-sm-2 placeholder" style="height: 180px; background-color:#ffaf3e;border-radius: 50%;color: #fff;font-weight: bold;font-size: 18px;">
                            <br><br><br>
                            <span><?= $total_article ?></span>
                            <p>总阅读数</p>
                        </div>-->
            <div class="table-responsive col-xs-6 col-sm-9 placeholder">
                <!--<p><em>备注：</em>黄色单元格表示阅读量大于5000.</p>-->
                <table class="table table-bordered  table-hover table-condensed">
                    <thead><tr style='font-weight:bold;'><td>日期</td><td>群名</td><td>管理部门</td><td>成员数</td></tr></thead>
                    <tbody>
                        <?php
                        foreach ($dbh->query($sql_wx_group) as $wx_group) {
                            echo '<tr>';
                            echo '<td>' . $blog_date . '</td>';
                            echo '<td>' . $wx_group['name'] . '</td>';
                            echo '<td>' . $wx_group['manager'] . '</td>';
                            echo '<td>' . $wx_group['num'] . '</td>';
                            echo '</tr>';
                        }
                        ?>
                    </tbody>
                </table>
            </div>            
        </div>        
    </div>
    </div>
    <?php
    $dbh = null;
// 现在运行完成，在此关闭连接
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
include_once 'template/footer.php';
?>